package com.example.zhenyi;

import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.scene.control.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.Date;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class ZhenyiController {

    private static final Logger logger = LoggerFactory.getLogger(ZhenyiController.class);

    @FXML
    private TextArea showMsgTextArea;

    @FXML
    private Button zhenyiExportButton;

    @FXML
    private Button yesterdayToExcelButton;


    @FXML
    private Label LabelEnd;

    @FXML
    private Label LabelStart;

    @FXML
    private DatePicker dataPickerStart;

    @FXML
    private DatePicker datePickerEnd;
    @FXML
    private Button exportDateButton;
    @FXML
    void endDate(ActionEvent event) {
        if(datePickerEnd.getValue().compareTo( dataPickerStart.getValue()) <0){
            Alert alert = new Alert(Alert.AlertType.WARNING,"終止日期要大於起始日期");
            alert.setTitle("日期選擇錯誤");
            alert.showAndWait();

        }

    }

    @FXML
    void startDate(ActionEvent event) {
        if(datePickerEnd.getValue().compareTo( dataPickerStart.getValue()) <0){
            Alert alert = new Alert(Alert.AlertType.WARNING,"終止日期要大於起始日期");
            alert.setTitle("日期選擇錯誤");
            alert.showAndWait();

        }
    }





    @FXML
    int dateExport(ActionEvent event) {

        String dateStart = String.valueOf(dataPickerStart.getValue())+" 00:00:00";
        //showMsgTextArea.appendText(dateStart + "\n");
        String dateEnd = String.valueOf(datePickerEnd.getValue()) + " 23:59:59";

        String localDateStartString = String.valueOf(dataPickerStart.getValue());
        String localDateEndString   = String.valueOf(datePickerEnd.getValue());
        //Alert alert1 = new Alert(Alert.AlertType.ERROR,"終止日期要大於起始日期");

        LocalDate localDateStart = dataPickerStart.getValue();
        LocalDate localDateEnd   = datePickerEnd.getValue();

        Date dateStart1 = Date.from(dataPickerStart.getValue().atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());
        Date dateEnd1 = Date.from(datePickerEnd.getValue().atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());

        if(datePickerEnd.getValue().compareTo( dataPickerStart.getValue()) <0){
            Alert alert = new Alert(Alert.AlertType.WARNING,"終止日期要大於起始日期");
            alert.setTitle("日期選擇錯誤");
            alert.showAndWait();
            return 0;
        }

        Thread thread = new Thread(()->{
            try {

                logger.info("導出指定日期數據");
                long l = System.currentTimeMillis();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = dateFormat.format(l);
                Connection connection = dbConnector.connect();
                if(!connection.isClosed()){
                    System.out.println("Succeeded connecting to the sqlserver database!");

                    showMsgTextArea.appendText(time+"--- "+"成功连接到Microsoft SQL Server 2008 !\n");
                }

                ResultSet resultSet = null;
                String sql = "select  tjdjb.tjrq 体检日期,tjdjb.XM 姓名,\n" +
                        "--case when jianchazhuangtai.ZT='0' then '未检' when jianchazhuangtai.ZT='1' then '已检' end 体检状态,\n" +
                        "case when tjdjb.XB='1' then '男' when tjdjb.XB='0' then '女' end 性别,tjdjb.NL 年龄,tjdjb.SFZH 身份证,\n" +
                        "shengao.ITEM_RESULT   AS \"身高(Cm)\",\n" +
                        "tizhong.ITEM_RESULT   AS \"体重(Kg)\",\n" +
                        "bmi.ITEM_RESULT  BMI指数,\n" +
                        "xueya.ITEM_RESULT   AS \"血压(mmHg)\",\n" +
                        "xinlv.ITEM_RESULT   AS \"心率(次/分钟)\",\n" +
                        "xuehongdanbai.ITEM_RESULT   AS \"血红蛋白(g/L)\",\n" +
                        "baixibao.ITEM_RESULT   AS \"白细胞(10^9/L)\",\n" +
                        "xuexiaoban.ITEM_RESULT   AS \"血小板(10^9/L)\",\n" +
                        "zhongxingli.ITEM_RESULT   AS \"中性粒细胞绝对值(10^9/L)\",\n" +
                        "yinxue.ITEM_RESULT  隐血,\n" +
                        "niaodanbai.ITEM_RESULT  尿蛋白,\n" +
                        "ALT.ITEM_RESULT   AS \"ALT(IU/L)\",\n" +
                        "AST.ITEM_RESULT   AS \"AST(IU/L)\",\n" +
                        "jigan.ITEM_RESULT    AS \"肌酐(mg/L)\",\n" +
                        "niaosu.ITEM_RESULT    AS \"尿素(mmol/L)\",\n" +
                        "niaosuan.ITEM_RESULT    AS \"尿酸(mmol/L)\",\n" +
                        "xindiantu.ITEM_RESULT   心电图,\n" +
                        "xiongpian.ITEM_RESULT   胸片,\n" +
                        "xuetang.ITEM_RESULT    AS \"血糖(mmol/L)\",\n" +
                        "zongdanguchun.ITEM_RESULT    AS \"总胆固醇(mmol/L)\",\n" +
                        "dimidu.ITEM_RESULT    AS \"低密度脂蛋白(mg/dL)\",\n" +
                        "ganyousanzhi.ITEM_RESULT    AS \"甘油三酯(mmol/L)\",\n" +
                        "sejue.ITEM_RESULT   色觉,\n" +
                        "shilizuo.ITEM_RESULT   视力左,\n" +
                        "shiliyou.ITEM_RESULT   视力右,\n" +
                        "jzshilizuo.ITEM_RESULT   矫正视力左,\n" +
                        "jzshiliyou.ITEM_RESULT   矫正视力右,\n" +
                        "tingli.ITEM_RESULT   听阈值,\n" +
                        "chaosheng.ITEM_RESULT   AS 超声\n" +
                        "from dbo.djd tjdjb\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') shengao on tjdjb.TJBH = shengao.TJBH and shengao.ITEM_ID = '010001'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') tizhong on tjdjb.TJBH = tizhong.TJBH and tizhong.ITEM_ID = '010002'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') bmi on tjdjb.TJBH = bmi.TJBH and bmi.ITEM_ID = '010007'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xueya on tjdjb.TJBH = xueya.TJBH and xueya.ITEM_ID = '010003'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xinlv on tjdjb.TJBH = xinlv.TJBH and xinlv.ITEM_ID = '010004'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xuehongdanbai on tjdjb.TJBH = xuehongdanbai.TJBH and xuehongdanbai.ITEM_ID = '320003'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') baixibao on tjdjb.TJBH = baixibao.TJBH and baixibao.ITEM_ID = '320151'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xuexiaoban on tjdjb.TJBH = xuexiaoban.TJBH and xuexiaoban.ITEM_ID = '320009'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') zhongxingli on tjdjb.TJBH = zhongxingli.TJBH and zhongxingli.ITEM_ID = '060267'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') yinxue on tjdjb.TJBH = yinxue.TJBH and yinxue.ITEM_ID = '060335'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') niaodanbai on tjdjb.TJBH = niaodanbai.TJBH and niaodanbai.ITEM_ID = '320030'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') ALT on tjdjb.TJBH = ALT.TJBH and ALT.ITEM_ID = '060316'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') AST on tjdjb.TJBH = AST.TJBH and AST.ITEM_ID = '060317'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') jigan on tjdjb.TJBH = jigan.TJBH and jigan.ITEM_ID = '060052'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') niaosu on tjdjb.TJBH = niaosu.TJBH and niaosu.ITEM_ID = '060318'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') niaosuan on tjdjb.TJBH = niaosuan.TJBH and niaosuan.ITEM_ID = '060053'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xindiantu on tjdjb.TJBH = xindiantu.TJBH and xindiantu.ITEM_ID = '110020'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xiongpian on tjdjb.TJBH = xiongpian.TJBH and xiongpian.ITEM_ID = '070007'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') xuetang on tjdjb.TJBH = xuetang.TJBH and xuetang.ITEM_ID = '060136'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') zongdanguchun on tjdjb.TJBH = zongdanguchun.TJBH and zongdanguchun.ITEM_ID = '060061'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') dimidu on tjdjb.TJBH = dimidu.TJBH and dimidu.ITEM_ID = '060063'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') ganyousanzhi on tjdjb.TJBH = ganyousanzhi.TJBH and ganyousanzhi.ITEM_ID = '060060'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') sejue on tjdjb.TJBH = sejue.TJBH and sejue.ITEM_ID = '040026'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') shilizuo on tjdjb.TJBH = shilizuo.TJBH and shilizuo.ITEM_ID = '040003'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') shiliyou on tjdjb.TJBH = shiliyou.TJBH and shiliyou.ITEM_ID = '040002'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') jzshilizuo on tjdjb.TJBH = jzshilizuo.TJBH and jzshilizuo.ITEM_ID = '040005'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') jzshiliyou on tjdjb.TJBH = jzshiliyou.TJBH and jzshiliyou.ITEM_ID = '040004'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') tingli on tjdjb.TJBH = tingli.TJBH and tingli.ITEM_ID = '050014'\n" +
                        "left join (select tjmx.tjbh,tjmx.item_id,tjmx.item_result from  dbo.tjmx tjmx where tjmx.EXAM_DATE >= '" + localDateStart + "') chaosheng on tjdjb.TJBH = chaosheng.TJBH and chaosheng.ITEM_ID = '230067'\n\n" +
                        "--left join futian_user.TJ_HISJK_RYXX jianchazhuangtai on tjdjb.TJBH = jianchazhuangtai.TJBH\n" +
                        "--where DWBH in (select BH from dbo.DWST where MC like '%振宜%') \n" +
                        "where tjdjb.DWBH = '7268'\n" +
                        //"and tjdjb.TJBH > '2213562' \n" +
                        //"and tjdjb.djlsh > '2310010001'  \n" +
                        //"and tjdjb.djlsh > (select min(djb.djlsh) from dbo.djd  djb where cast(tjrq as date) = '"+localDateStart+"')  \n" +
                        "and cast(tjdjb.tjrq as date) >= '"+localDateStart+"' and cast(tjdjb.tjrq as date) <=  '" +localDateEnd+"'\n"+
                        "order by tjrq ";
                        //"and TJRQ >=  '" + dateStart +"' and TJRQ <='"+dateEnd + "'";
                        //"and TJRQ between  '" + dateStart +"' and '"+dateEnd + "'";
                        //"and tjdjb.djlsh < (select max(djb.djlsh) from dbo.djd  djb where cast(tjrq as date) = '"+localDateEnd+"')  \n";
                try {
                    Statement statement = connection.createStatement();
                    Thread.sleep(1000);
                    System.out.println("开始查询 "+  dateStart +" - " + dateEnd + "的數據...");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"开始查询 "+  dateStart +" 至 " + dateEnd + "的數據...\n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"由于需要查询的数据量较大，需要 天数/秒，取決於查询天数長短，请耐心等待\n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面\n");

                    resultSet = statement.executeQuery(sql);
                    System.out.println("查询完毕");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"查询完毕 !\n");

                } catch (SQLException e) {
                    System.out.println("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState());
                    e.printStackTrace();
                    showMsgTextArea.appendText("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState()+"\n");
                    throw new RuntimeException(e);
                }
                XSSFWorkbook workbook = new XSSFWorkbook();
                String userPath = System.getProperty("user.home");
                l = System.currentTimeMillis();
                SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyyMMdd-HHmmss");
                time = dateFormat1.format(l);
                String desktopPath = userPath + "\\Desktop\\振宜数据"+time+".xlsx";
                System.out.println("desktop path:"+desktopPath);

                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面，路径为： "+ desktopPath +"\n");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"输出数据到excel中 ... \n");
                FileOutputStream fileOutputStream = new FileOutputStream(desktopPath);

                XSSFSheet sheet = workbook.createSheet("sheet1");

                Row headerRow = sheet.createRow(0);
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount ; i++) {
                    Cell cell = headerRow.createCell(i-1);
                    cell.setCellValue(metaData.getColumnName(i));
                }

                int rowNum = 1;
                while (resultSet.next()){
                    Row dataRow = sheet.createRow(rowNum++);
                    for (int i = 1; i <= columnCount; i++) {
                        Cell cell = dataRow.createCell(i-1);
                        cell.setCellValue(resultSet.getString(i));
                    }
                }

                workbook.write(fileOutputStream);
                System.out.println("写入文件完毕完毕");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"写入文件完毕 !\n");

                workbook.close();

                fileOutputStream.close();
                resultSet.close();
                connection.close();
                System.out.println("关闭所有数据库连接");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"关闭所有数据库连接 !\n");
                logger.info("导出结束");
            }catch (Exception e){
                System.out.println("error msg:"+e.getMessage());
                showMsgTextArea.appendText("error msg:"+e.getMessage()+"\n");
            }

        });
        thread.start();

        return 0;
    }

    @FXML
    void zhenyiExport(ActionEvent event) throws SQLException, IOException {

        Thread thread = new Thread(()->{
            try {
                logger.info("導出當日數據");
                long l = System.currentTimeMillis();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = dateFormat.format(l);
                Connection connection = dbConnector.connect();
                if(!connection.isClosed()){
                    System.out.println("Succeeded connecting to the sqlserver database!");

                    showMsgTextArea.appendText(time+"--- "+"成功连接到Microsoft SQL Server 2008 !\n");
                }

                ResultSet resultSet = null;
                String sql = "select  tjrq 体检日期,XM 姓名,\n" +
                        "--case when jianchazhuangtai.ZT='0' then '未检' when jianchazhuangtai.ZT='1' then '已检' end 体检状态,\n" +
                        "case when XB='1' then '男' when XB='0' then '女' end 性别,NL 年龄,SFZH 身份证,\n" +
                        "cast(shengao.ITEM_RESULT as varchar(1000)) + cast(shengao.UNIT as varchar(1000))AS 身高,\n" +
                        "cast(tizhong.ITEM_RESULT as varchar(1000)) + cast(tizhong.UNIT as varchar(1000))AS 体重,\n" +
                        "cast(bmi.ITEM_RESULT as varchar(1000)) + cast(bmi.UNIT as varchar(1000))AS BMI指数,\n" +
                        "cast(xueya.ITEM_RESULT as varchar(1000)) + cast(xueya.UNIT as varchar(1000))AS 血压,\n" +
                        "cast(xinlv.ITEM_RESULT as varchar(1000)) + cast(xinlv.UNIT as varchar(1000))AS 心率,\n" +
                        "cast(xuehongdanbai.ITEM_RESULT as varchar(1000)) + cast(xuehongdanbai.UNIT as varchar(1000))AS 血红蛋白,\n" +
                        "cast(baixibao.ITEM_RESULT as varchar(1000)) + cast(baixibao.UNIT as varchar(1000))AS 白细胞,\n" +
                        "cast(xuexiaoban.ITEM_RESULT as varchar(1000)) + cast(xuexiaoban.UNIT as varchar(1000))AS 血小板,\n" +
                        "cast(zhongxingli.ITEM_RESULT as varchar(1000)) + cast(zhongxingli.UNIT as varchar(1000))AS 中性粒细胞绝对值,\n" +
                        "cast(yinxue.ITEM_RESULT as varchar(1000)) + cast(yinxue.UNIT as varchar(1000))AS 隐血,\n" +
                        "cast(niaodanbai.ITEM_RESULT as varchar(1000)) + cast(niaodanbai.UNIT as varchar(1000))AS 尿蛋白,\n" +
                        "cast(ALT.ITEM_RESULT as varchar(1000)) + cast(ALT.UNIT as varchar(1000))AS ALT,\n" +
                        "cast(AST.ITEM_RESULT as varchar(1000)) + cast(AST.UNIT as varchar(1000))AS AST,\n" +
                        "cast(jigan.ITEM_RESULT  as varchar(1000)) + cast(jigan.UNIT as varchar(1000))AS 肌酐,\n" +
                        "cast(niaosu.ITEM_RESULT  as varchar(1000)) + cast(niaosu.UNIT as varchar(1000))AS 尿素,\n" +
                        "cast(niaosuan.ITEM_RESULT  as varchar(1000)) + cast(niaosuan.UNIT as varchar(1000))AS 尿酸,\n" +
                        "cast(xindiantu.ITEM_RESULT  as varchar(1000)) + cast(xindiantu.UNIT as varchar(1000))AS 心电图,\n" +
                        "cast(xiongpian.ITEM_RESULT  as varchar(1000)) + cast(xiongpian.UNIT as varchar(1000))AS 胸片,\n" +
                        "cast(xuetang.ITEM_RESULT  as varchar(1000)) + cast(xuetang.UNIT as varchar(1000))AS 血糖,\n" +
                        "cast(zongdanguchun.ITEM_RESULT  as varchar(1000)) + cast(zongdanguchun.UNIT as varchar(1000))AS 总胆固醇,\n" +
                        "cast(dimidu.ITEM_RESULT  as varchar(1000)) + cast(dimidu.UNIT as varchar(1000))AS 低密度,\n" +
                        "cast(ganyousanzhi.ITEM_RESULT  as varchar(1000)) + cast(ganyousanzhi.UNIT as varchar(1000))AS 甘油三酯,\n" +
                        "cast(sejue.ITEM_RESULT  as varchar(1000)) + cast(sejue.UNIT as varchar(1000))AS 色觉,\n" +
                        "cast(shilizuo.ITEM_RESULT  as varchar(1000)) + cast(shilizuo.UNIT as varchar(1000))AS 视力左,\n" +
                        "cast(shiliyou.ITEM_RESULT  as varchar(1000)) + cast(shiliyou.UNIT as varchar(1000))AS 视力右,\n" +
                        "cast(jzshilizuo.ITEM_RESULT  as varchar(1000)) + cast(jzshilizuo.UNIT as varchar(1000))AS 矫正视力左,\n" +
                        "cast(jzshiliyou.ITEM_RESULT  as varchar(1000)) + cast(jzshiliyou.UNIT as varchar(1000))AS 矫正视力右,\n" +
                        "cast(tingli.ITEM_RESULT  as varchar(1000)) + cast(tingli.UNIT as varchar(1000))AS 听阈值,\n" +
                        "cast(chaosheng.ITEM_RESULT  as varchar(1000)) + cast(chaosheng.UNIT as varchar(1000))AS 超声\n" +
                        "from dbo.djd tjdjb\n" +
                        "left join dbo.tjmx shengao on tjdjb.TJBH = shengao.TJBH and shengao.ITEM_ID = '010001'\n" +
                        "left join dbo.tjmx tizhong on tjdjb.TJBH = tizhong.TJBH and tizhong.ITEM_ID = '010002'\n" +
                        "left join dbo.tjmx bmi on tjdjb.TJBH = bmi.TJBH and bmi.ITEM_ID = '010007'\n" +
                        "left join dbo.tjmx xueya on tjdjb.TJBH = xueya.TJBH and xueya.ITEM_ID = '010003'\n" +
                        "left join dbo.tjmx xinlv on tjdjb.TJBH = xinlv.TJBH and xinlv.ITEM_ID = '010004'\n" +
                        "left join dbo.tjmx xuehongdanbai on tjdjb.TJBH = xuehongdanbai.TJBH and xuehongdanbai.ITEM_ID = '320003'\n" +
                        "left join dbo.tjmx baixibao on tjdjb.TJBH = baixibao.TJBH and baixibao.ITEM_ID = '320151'\n" +
                        "left join dbo.tjmx xuexiaoban on tjdjb.TJBH = xuexiaoban.TJBH and xuexiaoban.ITEM_ID = '320009'\n" +
                        "left join dbo.tjmx zhongxingli on tjdjb.TJBH = zhongxingli.TJBH and zhongxingli.ITEM_ID = '060267'\n" +
                        "left join dbo.tjmx yinxue on tjdjb.TJBH = yinxue.TJBH and yinxue.ITEM_ID = '060335'\n" +
                        "left join dbo.tjmx niaodanbai on tjdjb.TJBH = niaodanbai.TJBH and niaodanbai.ITEM_ID = '320030'\n" +
                        "left join dbo.tjmx ALT on tjdjb.TJBH = ALT.TJBH and ALT.ITEM_ID = '060316'\n" +
                        "left join dbo.tjmx AST on tjdjb.TJBH = AST.TJBH and AST.ITEM_ID = '060317'\n" +
                        "left join dbo.tjmx jigan on tjdjb.TJBH = jigan.TJBH and jigan.ITEM_ID = '060052'\n" +
                        "left join dbo.tjmx niaosu on tjdjb.TJBH = niaosu.TJBH and niaosu.ITEM_ID = '060318'\n" +
                        "left join dbo.tjmx niaosuan on tjdjb.TJBH = niaosuan.TJBH and niaosuan.ITEM_ID = '060053'\n" +
                        "left join dbo.tjmx xindiantu on tjdjb.TJBH = xindiantu.TJBH and xindiantu.ITEM_ID = '110020'\n" +
                        "left join dbo.tjmx xiongpian on tjdjb.TJBH = xiongpian.TJBH and xiongpian.ITEM_ID = '070007'\n" +
                        "left join dbo.tjmx xuetang on tjdjb.TJBH = xuetang.TJBH and xuetang.ITEM_ID = '060136'\n" +
                        "left join dbo.tjmx zongdanguchun on tjdjb.TJBH = zongdanguchun.TJBH and zongdanguchun.ITEM_ID = '060061'\n" +
                        "left join dbo.tjmx dimidu on tjdjb.TJBH = dimidu.TJBH and dimidu.ITEM_ID = '060063'\n" +
                        "left join dbo.tjmx ganyousanzhi on tjdjb.TJBH = ganyousanzhi.TJBH and ganyousanzhi.ITEM_ID = '060060'\n" +
                        "left join dbo.tjmx sejue on tjdjb.TJBH = sejue.TJBH and sejue.ITEM_ID = '040026'\n" +
                        "left join dbo.tjmx shilizuo on tjdjb.TJBH = shilizuo.TJBH and shilizuo.ITEM_ID = '040003'\n" +
                        "left join dbo.tjmx shiliyou on tjdjb.TJBH = shiliyou.TJBH and shiliyou.ITEM_ID = '040002'\n" +
                        "left join dbo.tjmx jzshilizuo on tjdjb.TJBH = jzshilizuo.TJBH and jzshilizuo.ITEM_ID = '040005'\n" +
                        "left join dbo.tjmx jzshiliyou on tjdjb.TJBH = jzshiliyou.TJBH and jzshiliyou.ITEM_ID = '040004'\n" +
                        "left join dbo.tjmx tingli on tjdjb.TJBH = tingli.TJBH and tingli.ITEM_ID = '050014'\n" +
                        "left join dbo.tjmx chaosheng on tjdjb.TJBH = chaosheng.TJBH and chaosheng.ITEM_ID = '230067'\n" +
                        "--left join futian_user.TJ_HISJK_RYXX jianchazhuangtai on tjdjb.TJBH = jianchazhuangtai.TJBH\n" +
                        "--where DWBH in (select BH from dbo.DWST where MC like '%振宜%')  \n" +
                        "where DWBH = '6262'\n" +
                        "and tjdjb.TJBH > '2213562' \n" +
                        "and DateDiff(dd,TJRQ,getdate()) = 0   ";
                try {
                    Statement statement = connection.createStatement();
                    Thread.sleep(1000);
                    System.out.println("开始查询");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"开始查询 ...  \n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"由于需要查询的数据量较大，大约需要3分钟左右，请耐心等待\n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面的《振宜当日.xlsx》，请保证此文件未被打开!\n");

                    resultSet = statement.executeQuery(sql);
                    System.out.println("查询完毕");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"查询完毕 !\n");

                } catch (SQLException e) {
                    System.out.println("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState());
                    e.printStackTrace();
                    showMsgTextArea.appendText("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState()+"\n");
                    throw new RuntimeException(e);
                }
                XSSFWorkbook workbook = new XSSFWorkbook();
                String userPath = System.getProperty("user.home");
                String desktopPath = userPath + "\\Desktop\\振宜当日.xlsx";
                System.out.println("desktop path:"+desktopPath);

                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面，路径为： "+ desktopPath +"\n");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"输出数据到excel中 ... \n");
                FileOutputStream fileOutputStream = new FileOutputStream(desktopPath);

                XSSFSheet sheet = workbook.createSheet("sheet1");

                Row headerRow = sheet.createRow(0);
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount ; i++) {
                    Cell cell = headerRow.createCell(i-1);
                    cell.setCellValue(metaData.getColumnName(i));
                }

                int rowNum = 1;
                while (resultSet.next()){
                    Row dataRow = sheet.createRow(rowNum++);
                    for (int i = 1; i <= columnCount; i++) {
                        Cell cell = dataRow.createCell(i-1);
                        cell.setCellValue(resultSet.getString(i));
                    }
                }

                workbook.write(fileOutputStream);
                System.out.println("写入文件完毕完毕");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"写入文件完毕 !\n");

                workbook.close();

                fileOutputStream.close();
                resultSet.close();
                connection.close();
                System.out.println("关闭所有数据库连接");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"关闭所有数据库连接 !\n");
            }catch (Exception e){
                System.out.println("error msg:"+e.getMessage());
                showMsgTextArea.appendText("error msg:"+e.getMessage()+"\n");
            }

        });
        thread.start();

    }


    @FXML
    void yesterdayToExcel(ActionEvent event) {

        Thread thread = new Thread(()->{
            logger.info("導出昨日數據");
            try {

                long l = System.currentTimeMillis();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = dateFormat.format(l);
                Connection connection = dbConnector.connect();
                if(!connection.isClosed()){
                    System.out.println("Succeeded connecting to the sqlserver database!");

                    showMsgTextArea.appendText(time+"--- "+"成功连接到Microsoft SQL Server 2008 !\n");
                }

                ResultSet resultSet = null;
                String sql = "select  tjrq 体检日期,XM 姓名,\n" +
                        "--case when jianchazhuangtai.ZT='0' then '未检' when jianchazhuangtai.ZT='1' then '已检' end 体检状态,\n" +
                        "case when XB='1' then '男' when XB='0' then '女' end 性别,NL 年龄,SFZH 身份证,\n" +
                        "cast(shengao.ITEM_RESULT as varchar(1000)) + cast(shengao.UNIT as varchar(1000))AS 身高,\n" +
                        "cast(tizhong.ITEM_RESULT as varchar(1000)) + cast(tizhong.UNIT as varchar(1000))AS 体重,\n" +
                        "cast(bmi.ITEM_RESULT as varchar(1000)) + cast(bmi.UNIT as varchar(1000))AS BMI指数,\n" +
                        "cast(xueya.ITEM_RESULT as varchar(1000)) + cast(xueya.UNIT as varchar(1000))AS 血压,\n" +
                        "cast(xinlv.ITEM_RESULT as varchar(1000)) + cast(xinlv.UNIT as varchar(1000))AS 心率,\n" +
                        "cast(xuehongdanbai.ITEM_RESULT as varchar(1000)) + cast(xuehongdanbai.UNIT as varchar(1000))AS 血红蛋白,\n" +
                        "cast(baixibao.ITEM_RESULT as varchar(1000)) + cast(baixibao.UNIT as varchar(1000))AS 白细胞,\n" +
                        "cast(xuexiaoban.ITEM_RESULT as varchar(1000)) + cast(xuexiaoban.UNIT as varchar(1000))AS 血小板,\n" +
                        "cast(zhongxingli.ITEM_RESULT as varchar(1000)) + cast(zhongxingli.UNIT as varchar(1000))AS 中性粒细胞绝对值,\n" +
                        "cast(yinxue.ITEM_RESULT as varchar(1000)) + cast(yinxue.UNIT as varchar(1000))AS 隐血,\n" +
                        "cast(niaodanbai.ITEM_RESULT as varchar(1000)) + cast(niaodanbai.UNIT as varchar(1000))AS 尿蛋白,\n" +
                        "cast(ALT.ITEM_RESULT as varchar(1000)) + cast(ALT.UNIT as varchar(1000))AS ALT,\n" +
                        "cast(AST.ITEM_RESULT as varchar(1000)) + cast(AST.UNIT as varchar(1000))AS AST,\n" +
                        "cast(jigan.ITEM_RESULT  as varchar(1000)) + cast(jigan.UNIT as varchar(1000))AS 肌酐,\n" +
                        "cast(niaosu.ITEM_RESULT  as varchar(1000)) + cast(niaosu.UNIT as varchar(1000))AS 尿素,\n" +
                        "cast(niaosuan.ITEM_RESULT  as varchar(1000)) + cast(niaosuan.UNIT as varchar(1000))AS 尿酸,\n" +
                        "cast(xindiantu.ITEM_RESULT  as varchar(1000)) + cast(xindiantu.UNIT as varchar(1000))AS 心电图,\n" +
                        "cast(xiongpian.ITEM_RESULT  as varchar(1000)) + cast(xiongpian.UNIT as varchar(1000))AS 胸片,\n" +
                        "cast(xuetang.ITEM_RESULT  as varchar(1000)) + cast(xuetang.UNIT as varchar(1000))AS 血糖,\n" +
                        "cast(zongdanguchun.ITEM_RESULT  as varchar(1000)) + cast(zongdanguchun.UNIT as varchar(1000))AS 总胆固醇,\n" +
                        "cast(dimidu.ITEM_RESULT  as varchar(1000)) + cast(dimidu.UNIT as varchar(1000))AS 低密度,\n" +
                        "cast(ganyousanzhi.ITEM_RESULT  as varchar(1000)) + cast(ganyousanzhi.UNIT as varchar(1000))AS 甘油三酯,\n" +
                        "cast(sejue.ITEM_RESULT  as varchar(1000)) + cast(sejue.UNIT as varchar(1000))AS 色觉,\n" +
                        "cast(shilizuo.ITEM_RESULT  as varchar(1000)) + cast(shilizuo.UNIT as varchar(1000))AS 视力左,\n" +
                        "cast(shiliyou.ITEM_RESULT  as varchar(1000)) + cast(shiliyou.UNIT as varchar(1000))AS 视力右,\n" +
                        "cast(jzshilizuo.ITEM_RESULT  as varchar(1000)) + cast(jzshilizuo.UNIT as varchar(1000))AS 矫正视力左,\n" +
                        "cast(jzshiliyou.ITEM_RESULT  as varchar(1000)) + cast(jzshiliyou.UNIT as varchar(1000))AS 矫正视力右,\n" +
                        "cast(tingli.ITEM_RESULT  as varchar(1000)) + cast(tingli.UNIT as varchar(1000))AS 听阈值,\n" +
                        "cast(chaosheng.ITEM_RESULT  as varchar(1000)) + cast(chaosheng.UNIT as varchar(1000))AS 超声\n" +
                        "from dbo.djd tjdjb\n" +
                        "left join dbo.tjmx shengao on tjdjb.TJBH = shengao.TJBH and shengao.ITEM_ID = '010001'\n" +
                        "left join dbo.tjmx tizhong on tjdjb.TJBH = tizhong.TJBH and tizhong.ITEM_ID = '010002'\n" +
                        "left join dbo.tjmx bmi on tjdjb.TJBH = bmi.TJBH and bmi.ITEM_ID = '010007'\n" +
                        "left join dbo.tjmx xueya on tjdjb.TJBH = xueya.TJBH and xueya.ITEM_ID = '010003'\n" +
                        "left join dbo.tjmx xinlv on tjdjb.TJBH = xinlv.TJBH and xinlv.ITEM_ID = '010004'\n" +
                        "left join dbo.tjmx xuehongdanbai on tjdjb.TJBH = xuehongdanbai.TJBH and xuehongdanbai.ITEM_ID = '320003'\n" +
                        "left join dbo.tjmx baixibao on tjdjb.TJBH = baixibao.TJBH and baixibao.ITEM_ID = '320151'\n" +
                        "left join dbo.tjmx xuexiaoban on tjdjb.TJBH = xuexiaoban.TJBH and xuexiaoban.ITEM_ID = '320009'\n" +
                        "left join dbo.tjmx zhongxingli on tjdjb.TJBH = zhongxingli.TJBH and zhongxingli.ITEM_ID = '060267'\n" +
                        "left join dbo.tjmx yinxue on tjdjb.TJBH = yinxue.TJBH and yinxue.ITEM_ID = '060335'\n" +
                        "left join dbo.tjmx niaodanbai on tjdjb.TJBH = niaodanbai.TJBH and niaodanbai.ITEM_ID = '320030'\n" +
                        "left join dbo.tjmx ALT on tjdjb.TJBH = ALT.TJBH and ALT.ITEM_ID = '060316'\n" +
                        "left join dbo.tjmx AST on tjdjb.TJBH = AST.TJBH and AST.ITEM_ID = '060317'\n" +
                        "left join dbo.tjmx jigan on tjdjb.TJBH = jigan.TJBH and jigan.ITEM_ID = '060052'\n" +
                        "left join dbo.tjmx niaosu on tjdjb.TJBH = niaosu.TJBH and niaosu.ITEM_ID = '060318'\n" +
                        "left join dbo.tjmx niaosuan on tjdjb.TJBH = niaosuan.TJBH and niaosuan.ITEM_ID = '060053'\n" +
                        "left join dbo.tjmx xindiantu on tjdjb.TJBH = xindiantu.TJBH and xindiantu.ITEM_ID = '110020'\n" +
                        "left join dbo.tjmx xiongpian on tjdjb.TJBH = xiongpian.TJBH and xiongpian.ITEM_ID = '070007'\n" +
                        "left join dbo.tjmx xuetang on tjdjb.TJBH = xuetang.TJBH and xuetang.ITEM_ID = '060136'\n" +
                        "left join dbo.tjmx zongdanguchun on tjdjb.TJBH = zongdanguchun.TJBH and zongdanguchun.ITEM_ID = '060061'\n" +
                        "left join dbo.tjmx dimidu on tjdjb.TJBH = dimidu.TJBH and dimidu.ITEM_ID = '060063'\n" +
                        "left join dbo.tjmx ganyousanzhi on tjdjb.TJBH = ganyousanzhi.TJBH and ganyousanzhi.ITEM_ID = '060060'\n" +
                        "left join dbo.tjmx sejue on tjdjb.TJBH = sejue.TJBH and sejue.ITEM_ID = '040026'\n" +
                        "left join dbo.tjmx shilizuo on tjdjb.TJBH = shilizuo.TJBH and shilizuo.ITEM_ID = '040003'\n" +
                        "left join dbo.tjmx shiliyou on tjdjb.TJBH = shiliyou.TJBH and shiliyou.ITEM_ID = '040002'\n" +
                        "left join dbo.tjmx jzshilizuo on tjdjb.TJBH = jzshilizuo.TJBH and jzshilizuo.ITEM_ID = '040005'\n" +
                        "left join dbo.tjmx jzshiliyou on tjdjb.TJBH = jzshiliyou.TJBH and jzshiliyou.ITEM_ID = '040004'\n" +
                        "left join dbo.tjmx tingli on tjdjb.TJBH = tingli.TJBH and tingli.ITEM_ID = '050014'\n" +
                        "left join dbo.tjmx chaosheng on tjdjb.TJBH = chaosheng.TJBH and chaosheng.ITEM_ID = '230067'\n" +
                        "--left join futian_user.TJ_HISJK_RYXX jianchazhuangtai on tjdjb.TJBH = jianchazhuangtai.TJBH\n" +
                        "--where DWBH in (select BH from dbo.DWST where MC like '%振宜%')  \n" +
                        "where DWBH = '6262'\n" +
                        "and tjdjb.TJBH > '2213562' \n" +
                        "and DateDiff(dd,TJRQ,getdate()) = 1   ";
                try {
                    Statement statement = connection.createStatement();
                    Thread.sleep(1000);
                    System.out.println("开始查询");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"开始查询 ...  \n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"由于需要查询的数据量较大，大约需要3分钟左右，请耐心等待\n");
                    Thread.sleep(1000);
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面的《振宜昨日.xlsx》，请保证此文件未被打开!\n");

                    resultSet = statement.executeQuery(sql);
                    System.out.println("查询完毕");
                    l = System.currentTimeMillis();
                    time = dateFormat.format(l);
                    showMsgTextArea.appendText(time+"--- "+"查询完毕 !\n");

                } catch (SQLException e) {
                    System.out.println("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState());
                    e.printStackTrace();
                    showMsgTextArea.appendText("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState()+"\n");
                    throw new RuntimeException(e);
                }
                XSSFWorkbook workbook = new XSSFWorkbook();
                String userPath = System.getProperty("user.home");
                String desktopPath = userPath + "\\Desktop\\振宜昨日.xlsx";
                System.out.println("desktop path:"+desktopPath);

                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"文件将输出到桌面，路径为： "+ desktopPath +"\n");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"输出数据到excel中 ... \n");
                FileOutputStream fileOutputStream = new FileOutputStream(desktopPath);

                XSSFSheet sheet = workbook.createSheet("sheet1");

                Row headerRow = sheet.createRow(0);
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount ; i++) {
                    Cell cell = headerRow.createCell(i-1);
                    cell.setCellValue(metaData.getColumnName(i));
                }

                int rowNum = 1;
                while (resultSet.next()){
                    Row dataRow = sheet.createRow(rowNum++);
                    for (int i = 1; i <= columnCount; i++) {
                        Cell cell = dataRow.createCell(i-1);
                        cell.setCellValue(resultSet.getString(i));
                    }
                }

                workbook.write(fileOutputStream);
                System.out.println("写入文件完毕完毕");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"写入文件完毕 !\n");

                workbook.close();

                fileOutputStream.close();
                resultSet.close();
                connection.close();
                System.out.println("关闭所有数据库连接");
                Thread.sleep(1000);
                l = System.currentTimeMillis();
                time = dateFormat.format(l);
                showMsgTextArea.appendText(time+"--- "+"关闭所有数据库连接 !\n");
            }catch (Exception e){
                System.out.println("error msg:"+e.getMessage());
                showMsgTextArea.appendText("error msg:"+e.getMessage()+"\n");
            }

        });
        thread.start();
    }
    @FXML
    public void initialize(){

        logger.info("初始化振宜导出。。。");

        dataPickerStart.setValue(LocalDate.now());
        datePickerEnd.setValue(LocalDate.now());
    }
}